﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Threading;
using bzxxxt.admin.entity;
using bzxxxt.admin.service;
using bzxxxt.bll;
using bzxxxt.dal;
using bzxxxt.model;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System.Collections;

namespace bzxxxt
{
    public partial class export : System.Web.UI.Page
    {
        private string[] GraveTypes = { "", "A", "B", "C" };
        private string[] GraveChars = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" };
        ImportBLL bll = new ImportBLL();
        bzxxxtEntities DB = new bzxxxtEntities();
        static List<ImportModel> listImport = new List<ImportModel>();
        static List<ParadiseLayoutModel> graveList = new List<ParadiseLayoutModel>();
        //Hashtable hGrave = new Hashtable();
        //Hashtable hArea = new Hashtable();

        Dictionary<string, string> hArea = new Dictionary<string, string>();
        Dictionary<string, int> hGrave = new Dictionary<string, int>();


        protected void Page_Load(object sender, EventArgs e)
        {
            ((IObjectContextAdapter)DB).ObjectContext.CommandTimeout = Cfg.TimeOut;
            
            this.Title = this.Title + "v1";
            UserEntity user = UserService.GetUser(HttpContext.Current);
            if (user == null)
            {
                Response.Write(JsonUtil.ToJson(new { success = false, content = "无权访问！" }));
                Response.End();
                return;
            }

            if (!IsPostBack)
            {
                DataTable dt = bll.GetParadiseList();
                if (dt == null || dt.Rows.Count == 0) return;
                foreach (DataRow dr in dt.Rows)
                {
                    plist.Items.Add(dr[0].ToString());
                }
                plist.DataBind();
            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string name = plist.SelectedItem.Text;
            DateTime dt = DateTime.Now;
            if (!string.IsNullOrEmpty(txtTime.Text))
            {
                DateTime.TryParse(txtTime.Text, out dt);
            }

            string f = bll.ExportParadiseData(name, dt);
            linkFile.NavigateUrl = f;
            linkFile.Text = f;
            //Response.Redirect(f);
        }



        protected void btnUpload_Click(object sender, EventArgs e)
        {
            UserEntity user = UserService.GetUser(HttpContext.Current);
            //文件名  

            string fileExt = System.IO.Path.GetExtension(fileUpLoad.FileName);
            if (!(fileExt == ".xls" || fileExt == ".xlsx"))
            {
                lblMsg.Text = "只支持扩展名为.xls或.xlsx文件！";
                return;
            }
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + fileUpLoad.FileName;
            fileUpLoad.SaveAs(Server.MapPath("~/admin/upload/") + fileName);
            linkUpload.NavigateUrl = "/admin/upload/" + fileName;
            linkUpload.Text = "/admin/upload/" + fileName;
            lblMsg.Text = "上传成功！";
        }

        protected void btnOnce_Click(object sender, EventArgs e)
        {
            DateTime st = DateTime.Now;
            Cfg.IsRunning = true;
            string filename = Server.MapPath(linkUpload.Text);
            lblMsg.Text = "正在导入数据2....";
            Thread th = new Thread(onceImport);
            th.Start();
            th.Join();
            DateTime et = DateTime.Now;
            TimeSpan ts = et - st;

            lblMsg.Text = string.Format("导入数据完成2,耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds);
        }
        private void onceImport()
        {
            importGrave();
            importdata();
        }

        protected void btnImport_Click(object sender, EventArgs e)
        {

            Cfg.IsRunning = true;
            string filename = Server.MapPath(linkUpload.Text);
            lblMsg.Text = "正在导入数据....";
            Thread th = new Thread(importdata);
            th.Start();
            th.Join();
            lblMsg.Text += "导入数据完成，请查看日志/admin/upload/log2.html！<br/>";
        }


        private void importdata()
        {
            Dictionary<string, tbRelative> hRelative = new Dictionary<string, tbRelative>();
            Dictionary<string, tbDead> hDead = new Dictionary<string, tbDead>();
            string path = Server.MapPath("/admin/upload/log2.html");
            StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create), Encoding.UTF8);
            DateTime st = DateTime.Now;
            sw.WriteLine(st.ToString("<br/>开始时间：yyyy-MM-dd HH:mm:ss"));
            int newDeads = 0;

            bool bOutputHint = true;
            StringBuilder sbMsg = new StringBuilder();
            int cnt = 0;
            int total = listImport.Count;
            if (bOutputHint)
            {
                Response.Write("start[" + total + "]<br/>");
                Response.Flush();
            }
            hRelative.Clear();
            hDead.Clear();
            hGrave.Clear();

            foreach (ImportModel mm in listImport)
            {
                if (!Cfg.IsRunning) break;
                cnt++;
                try
                {
                    tbRelative relative = new tbRelative();
                    relative.name = mm.rname;
                    relative.gender = mm.rsex;
                    if (string.IsNullOrEmpty(relative.gender)) relative.gender = "不详";
                    relative.cardno = mm.rcardno;
                    relative.tel = mm.rtel;
                    relative.areacode = mm.cun;
                    relative.addr = mm.zu;

                    tbDead dead = new tbDead();
                    dead.name = mm.dname;
                    dead.importId = mm.importId;
                    dead.relationid = mm.relation;
                    if (string.IsNullOrEmpty(dead.relationid)) dead.relationid = "不详";

                    dead.storetype = "遗灰";
                    if (!string.IsNullOrEmpty(mm.storeType))
                        dead.storetype = mm.storeType;
                    dead.gender = mm.dsex;
                    if (string.IsNullOrEmpty(dead.gender)) dead.gender = "不详";
                    dead.areacode = mm.cun;
                    dead.homeAddress = mm.zu;
                    int graveId = mm.graveId;
                    if (graveId == 0)
                    {
                        var gv = DB.tbGrave.FirstOrDefault(m => m.location.Substring(0, m.location.Length - 1) == mm.location.Substring(0, mm.location.Length - 1));
                        graveId = gv.id;
                    }
                    tbRelative dr = new tbRelative();
                    string key = relative.name + relative.areacode;
                    if (!string.IsNullOrEmpty(relative.cardno))
                    {
                        key = relative.name + relative.cardno;
                    }
                    else if (!string.IsNullOrEmpty(relative.tel))
                    {
                        key = relative.name + relative.areacode + relative.tel;
                    }
                    else if (!string.IsNullOrEmpty(relative.addr))
                    {
                        key = relative.name + relative.areacode + relative.addr;
                    }
                    if (!hRelative.ContainsKey(key))
                    {
                        //联系人
                        if (!string.IsNullOrEmpty(relative.cardno))
                        {
                            dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.cardno == relative.cardno);                            
                        }
                        else if (!string.IsNullOrEmpty(relative.tel))
                        {
                            dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.tel == relative.tel);
                        }
                        else if (!string.IsNullOrEmpty(relative.addr))
                        {
                            dr = DB.tbRelative.FirstOrDefault(m => m.name == relative.name && m.areacode == relative.areacode && m.addr == relative.addr);
                        }
                        
                        //dr = DB.tbRelative.FirstOrDefault(m => (m.name == relative.name && m.tel == relative.tel) || (relative.areacode != null && m.areacode == relative.areacode && m.name == relative.name) || (relative.addr != null && relative.addr == m.addr && relative.name == m.name) || (relative.cardno != null && relative.cardno.Length > 0 && m.cardno == relative.cardno));
                        
                        if (dr == null)
                        {
                            dr = new tbRelative();
                            dr.name = relative.name;
                            dr.gender = relative.gender;
                            dr.cardno = relative.cardno;
                            dr.tel = relative.tel;
                            dr.areacode = relative.areacode;
                            dr.addr = relative.addr;

                            DB.tbRelative.Add(dr);
                            hRelative.Add(key, dr);
                            
                        }
                        else
                        {
                            dr.name = relative.name;
                            if (relative.gender != "不详")
                                dr.gender = relative.gender;
                            if(!string.IsNullOrEmpty(relative.cardno))
                                dr.cardno = relative.cardno;
                            if (!string.IsNullOrEmpty(relative.tel))
                                dr.tel = relative.tel;
                            if (!string.IsNullOrEmpty(relative.addr))
                                dr.addr = relative.addr;
                            hRelative.Add(key, dr);
                        }
                    }
                    else
                    {
                        hRelative.TryGetValue(key, out dr);
                    }

                    var grave = DB.tbGrave.FirstOrDefault(m => m.id == graveId);
                    if (grave == null)
                    {
                        string msg = string.Format("<br/>{0}[{1}]穴位不存在！", mm.location, graveId);
                        sw.WriteLine(msg);
                        continue;
                    }
                    grave.paidTime = mm.regTime;
                    if (grave.relativeId == null)
                    {
                        grave.tbRelative = dr;
                    }
                    
                    var dd = DB.tbDead.FirstOrDefault(d=>d.importId==mm.importId);

                    if (dd == null)
                    {
                        dd = dead;
                        DB.tbDead.Add(dd);

                        //string msg = string.Format("<br/>新增死者姓名：{0},联系人：[{1},区：{2},排:{3},层:{4},号{5}]", dd.name, mm.rname, mm.section, mm.line, mm.layer, mm.col);
                        //sw.WriteLine(msg);
                        //sw.Flush();
                    }
                    else
                    {
                        //string msg = string.Format("姓名[{0}]区域[{1}]组[{2}]死者在系统中已经存在！<br/>", dead.name, dd.areacode, dead.homeAddress);
                        //sw.WriteLine(msg);
                        //dd.name = dead.name;
                        //dd.gender = dead.gender;
                        //dd.cardno = dead.cardno;
                        //dd.areacode = dead.areacode;
                        //dd.actFiretime = dead.actFiretime;
                        //dd.storetype = dead.storetype;
                        //dd.relationid = dead.relationid;
                    }

                    //if (dd.graveid == null && grave.qty >= grave.graveType) //为满的穴位            
                    //{
                    //    return JsonUtil.ToJson(new
                    //    {
                    //        success = false,
                    //        msg = string.Format("此穴位已满,寄存失败！")
                    //    });
                    //}
                    //建立穴位与死者的关系    
                    if (dd.graveid != null && dd.graveid != graveId)
                    {
                        string msg = string.Format("姓名[{0}]区域[{1}]组[{2}]死者已经寄存【{3}】！<br/>", dead.name, dd.tbArea == null ? "" : dd.tbArea.name, dead.homeAddress, "[" + dd.tbGrave.tbParadise.name + "]" + dd.tbGrave.name);
                        sw.WriteLine(msg);
                        continue;
                    }
                    if (dd.graveid == null)
                    {
                        dd.graveid = graveId;
                        grave.qty++;
                        dd.code = grave.location + GraveChars[grave.qty % 10];
                        if (mm.storeTime == null)
                        {
                            dd.starttime = DateTime.Now;
                            dd.endtime = DateTime.Now.AddYears(8);
                        }
                        else
                        {
                            dd.starttime = mm.storeTime;
                            dd.endtime = DateTime.Now.AddYears(8);
                        }
                        if (grave.qty == 1)
                        {
                            grave.description = "[" + mm.dname + "," + mm.oldgrave + "]";
                        }
                        else
                        {
                            grave.description += ",[" + mm.dname + "," + mm.oldgrave + "]";
                        }
                    }
                    if (string.IsNullOrEmpty(dd.code))
                    {
                        dd.code = grave.location + GraveTypes[grave.graveType % 4] + GraveChars[grave.qty % 10];
                    }

                    //建立死者与联系人的关系
                    if (dd.tbRelative == null)
                    {
                        if (dr.id > 0) dd.relativeid = dr.id;
                        else dd.tbRelative = dr;
                    }
                    else
                    {
                        dd.tbRelative.name = relative.name;
                        dd.tbRelative.gender = relative.gender;
                        dd.tbRelative.tel = relative.tel;
                        dd.tbRelative.areacode = relative.areacode;
                        dd.tbRelative.cardno = relative.cardno;
                        dd.tbRelative.addr = relative.addr;
                    }
                    //更新穴位的联系人信息
                    if (grave.qty == 1)
                    {
                        grave.tbRelative.name = relative.name;
                        grave.tbRelative.tel = relative.tel;
                        grave.tbRelative.areacode = relative.areacode;
                        grave.tbRelative.cardno = relative.cardno;
                        grave.tbRelative.addr = relative.addr;
                        grave.tbRelative.gender = relative.gender;

                    }

                }
                catch (Exception ex)
                {
                    string msg = string.Format("<br/>序号:{0}死者姓名:{1}导入未成功,可能原因:{2}<br/>", mm.id, mm.dname, ex.Message);
                    sw.WriteLine(msg);
                }

                if (cnt % 200 == 0)
                {
                    if (bOutputHint)
                    {
                        Response.Write("[" + cnt + "] ");
                        Response.Flush();
                    }
                    string msg = "<br/>[" + cnt + "]";
                    sw.WriteLine(msg);
                    sw.Flush();

                    DB.SaveChanges();

                    hRelative.Clear();
                }
            }
            DB.SaveChanges();
            if (bOutputHint)
            {
                Response.Write("<br/>end[" + total + "]<br/>");
                Response.Flush();
            }
            sw.WriteLine("<br/>导入处理数据结束！<br/>");
            DateTime et = DateTime.Now;
            TimeSpan ts = et - st;
            sw.WriteLine(et.ToString("<br/>导入时间：yyyy-MM-dd HH:mm:ss"));
            sw.WriteLine(string.Format("<br/>耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds));
            sw.Close();
        }
        
        protected void btnCreateGrave_Click(object sender, EventArgs e)
        {
            listImport.Clear();
            Cfg.IsRunning = true;
            string filename = Server.MapPath(linkUpload.Text);
            lblMsg.Text = "正在创建穴位....";
            Thread th = new Thread(importGrave);
            th.Start();
            th.Join();
            lblMsg.Text += "穴位创建成功，请查看日志/admin/upload/log.html，准备导入！<br/>";
        }

        protected void importGrave()
        {

            int cnt = 0;
            string path = Server.MapPath("/admin/upload/log.html");
            StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create), Encoding.UTF8);
            DateTime st = DateTime.Now;
            sw.WriteLine(st.ToString("开始时间：yyyy-MM-dd HH:mm:ss"));

            string strFileName = Server.MapPath(linkUpload.Text);

            int SheetIndex = 0;
            int HeaderRowIndex = 1;

            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);

            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "新栋", "新楼层", "新区", "新排", "新层", "新列", "原位置", "骨灰堂名", "穴状", "骨灰盒数", "登记日期", "联系姓名",
                                   "性别","联系电话","村居委","所在镇","家庭住址","身份证号","亡故者姓名","与联系者关系","骨灰类型","存放日期","区","排","号","死者ID"};
                //新栋	新楼层	新区	新排	新层	新列	原位置	骨灰堂名	穴状	骨灰盒数	登记日期	联系姓名	
                //性别	联系电话	村居委	所在镇	家庭住址	身份证号	亡故者姓名	与联系者关系	骨灰类型	存放日期	区	排	号 死者ID

                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;
                hGrave.Clear();
                listImport.Clear();
                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    if (!Cfg.IsRunning) break;
                    ImportModel m = new ImportModel();
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            string msg = string.Format("<br/>第{0}行无数据!", i + 1);
                            sw.WriteLine(msg);
                            continue;
                        }
                        m.id = i;

                        //新栋
                        string dong = getCellVaue(row.GetCell(0));
                        m.dong = int.Parse(dong);
                        //新楼层
                        string floor = getCellVaue(row.GetCell(1));
                        m.floor = int.Parse(floor);
                        //新区
                        m.SectionStr = getCellVaue(row.GetCell(2));
                        m.SectionStr = Utils.getSectionStr(m.SectionStr);

                        //新排
                        m.LineStr = "00"+getCellVaue(row.GetCell(3));
                        m.LineStr = m.LineStr.Substring(m.LineStr.Length - 2);

                        //新层
                        string layer = getCellVaue(row.GetCell(4));
                        m.layer = int.Parse(layer);

                        //5新列
                        string col = getCellVaue(row.GetCell(5));
                        m.col = int.Parse(col);

                        //原位置
                        string oldgrave = getCellVaue(row.GetCell(6));
                        m.oldgrave = oldgrave;

                        //骨灰堂名
                        string paradise = getCellVaue(row.GetCell(7));
                        m.pname = paradise;
                        //处理骨灰堂
                        var p = DB.tbParadise.FirstOrDefault(mp => mp.name == paradise);
                        if (p == null)
                        {
                            string msg = string.Format("<br/>第{0}行骨灰堂【{1}】无效! ", i + 1, paradise);
                            sw.WriteLine(msg);
                            continue;
                        }
                        else
                        {
                            m.pcode = p.code;
                        }


                        //穴状 8
                        string gravetype = getCellVaue(row.GetCell(8));
                        if (gravetype.IndexOf('单') >= 0) m.gtype = 1;
                        else if (gravetype.IndexOf('双') >= 0) m.gtype = 2;
                        else m.gtype = 3;

                        //骨灰盒数
                        string qty = getCellVaue(row.GetCell(9));
                        m.qty = int.Parse(qty);

                        //登记日期
                        string regTime = getCellVaue(row.GetCell(10));
                        try
                        {
                            if (!string.IsNullOrEmpty(regTime)) m.regTime = DateTime.Parse(regTime);
                        }
                        catch  
                        {
                            string msg = string.Format("第{0}行登记日期【{1}】无效! ", i + 1, regTime);
                            sw.WriteLine(msg);
                        }

                        //联系姓名11
                        string rname = getCellVaue(row.GetCell(11));
                        if (string.IsNullOrEmpty(rname))
                        {
                            //string msg = string.Format("第{0}行户主姓名不能为空! ", i + 1);
                            //sw.WriteLine(msg);
                            //continue;
                            rname = "不详";
                        }
                        m.rname = rname;


                        //性别
                        string rsex = getCellVaue(row.GetCell(12));
                        m.rsex = rsex;


                        //联系电话
                        string rtel = getCellVaue(row.GetCell(13));
                        m.rtel = rtel;

                        //所在镇15
                        string zheng = getCellVaue(row.GetCell(15));

                        //家庭住址
                        string zu = getCellVaue(row.GetCell(16));
                        m.zu = zu;

                        string cun = getCellVaue(row.GetCell(14));
                        if (string.IsNullOrEmpty(cun))
                        {
                            cun = "不详";
                        }
                        //村居委
                        if (!hArea.ContainsKey(cun))
                        {
                            var c = DB.tbArea.FirstOrDefault(area => area.name.IndexOf(cun) >= 0);
                            if (c == null)
                            {
                                string msg = string.Format("<br/>第{0}行村别【{1}】无效! ", i + 1, cun);
                                sw.WriteLine(msg);
                                continue;
                            }
                            else
                            {
                                m.cun = c.code;
                                hArea.Add(cun, c.code);
                            }
                        }
                        else
                        {
                            string c;
                            hArea.TryGetValue(cun, out c);
                            m.cun = c;
                        }


                        //身份证号
                        string rcardno = getCellVaue(row.GetCell(17));
                        m.rcardno = rcardno;
                        if (m.rcardno.Length > 18) m.rcardno = m.rcardno.Substring(0, 18);

                        //亡故者姓名
                        string dname = getCellVaue(row.GetCell(18));
                        if (string.IsNullOrEmpty(dname))
                        {
                            string msg = string.Format("<br/>第{0}行死者姓名不能为空! ", i + 1);
                            sw.WriteLine(msg);
                            continue;
                        }
                        else
                        {
                            m.dname = dname;
                        }

                        //与联系者关系19
                        string relation = getCellVaue(row.GetCell(19));
                        m.relation = relation;

                        //string[] headers = { "新栋", "新楼层", "新区", "新排", "新层", "新列", "原位置", "骨灰堂名",  "穴状", "骨灰盒数", "登记日期", "联系姓名",
                        //          "性别","联系电话","村居委","所在镇","家庭住址","身份证号","亡故者姓名","与联系者关系","骨灰类型","存放日期"};

                        //骨灰类型
                        string storetype = getCellVaue(row.GetCell(20));
                        m.storeType = storetype;
                        m.dsex = "不详";

                        string storetime = getCellVaue(row.GetCell(21));
                        try
                        {
                            if (!string.IsNullOrEmpty(storetime))
                            {
                                m.storeTime = DateTime.Parse(storetime);
                                if (m.regTime == null) m.storeTime = m.storeTime;
                            }
                        }
                        catch
                        {
                            string msg = string.Format("第{0}行存放日期【{1}】无效! ", i + 1, storetime);
                            sw.WriteLine(msg);
                        }

                        //死者ID
                        string importId = getCellVaue(row.GetCell(25));
                        m.importId = int.Parse(importId);
                        
                        //测试新的存放位置是否存在
                        string loc = m.location;
                        string sectionNum = m.SectionStr.Substring(m.SectionStr.Length - 2);
                        string locName = string.Format("{1}栋{2}楼{3}区{4}排{5:00}层{6:000}号", m.pname, m.dong, m.floor, sectionNum, m.LineStr, m.layer, m.col);
                        
                        if (p.type != "骨灰堂") //如果不是骨灰堂
                        {
                            char cc = Convert.ToChar('A' + m.gtype - 1);
                            sectionNum = m.SectionStr;
                            loc = string.Format("{0:000}{1}{2}{3:00000}{4:0}", m.pcode, Utils.getSectionStr4Bit(m.SectionStr), m.LineStr, m.col, cc);                            
                            locName = string.Format("{0}区{1}排{2:00000}号", sectionNum, m.LineStr, m.col);
                            m.layer = m.col / 1000 % 100;
                            m.col = m.col % 1000;
                            floor = "0";
                            
                        }
                        string lc = loc.Substring(0, loc.Length - 1);
                        if (!hGrave.ContainsKey(lc))
                        {
                            var g = DB.tbGrave.FirstOrDefault(mg => mg.location.Substring(0, mg.location.Length - 1) == lc);
                            if (g == null)
                            {
                                hGrave.Add(lc, 0);
                                GraveBLL gb = new GraveBLL();
                                tbGrave grave = new tbGrave
                                {
                                    location = loc,
                                    name = locName,

                                    hourseNum = dong,
                                    floorNum = floor,
                                    sectionNum = sectionNum,
                                    lineNum = m.LineStr,
                                    layerNum = string.Format("{0:00}", m.layer),
                                    columnNum = string.Format("{0:000}", m.col),
                                    qty = 0,
                                    importloc = m.oldgrave,
                                    graveType = m.gtype
                                };

                                var pd = DB.tbParadise.FirstOrDefault(pr => pr.code == m.pcode);
                                grave.paradiseId = pd.id;
                                DB.tbGrave.Add(grave);

                                //string msg = string.Format("<br/>第{0}行存放位置{1}不存在!,新增插入 ", i + 1, grave.location);
                                //sw.WriteLine(msg);
                                m.graveId = 0;
                                cnt++;

                            }
                            else
                            {
                                m.graveId = g.id;
                                hGrave.Add(lc, g.id);
                            }
                        }

                        listImport.Add(m);
                    }
                    catch (Exception ex1)
                    {
                        string msg = string.Format(" <br/>第{0}行数据格式错误!" + ex1.Message, i + 1);
                        sw.WriteLine(msg);
                        sbLine.Clear();
                    }

                    if (i % 500 == 0)
                    {
                        string msg = "<br/>" + DateTime.Now.ToString("[HH:mm:ss]") + "[" + i + "]";
                        sw.WriteLine(msg);
                        sw.Flush();
                        Response.Write("[" + i + "] ");
                        Response.Flush();
                        DB.SaveChanges();
                        hGrave.Clear();
                    }
                }
                string msg2 = string.Format("<br/>ok[{0}],新增穴位:[{1}]<br/>", rowCount, cnt);
                sw.WriteLine(msg2);
                sw.Flush();
            }
            catch (Exception ex)
            {
                lblMsg.Text = ex.Message;
            }


            DateTime et = DateTime.Now;
            TimeSpan ts = et - st;
            sw.WriteLine(et.ToString("<br/>扫描时间：yyyy-MM-dd HH:mm:ss"));
            sw.WriteLine(string.Format("<br/>扫描耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds));
            sw.Flush();

            DB.SaveChanges();
            //listImport.ForEach(g =>
            //{
            //    if (g.graveId == 0)
            //    {
            //        var gv = DB.tbGrave.FirstOrDefault(m => m.location.Substring(0, m.location.Length - 1) == g.location.Substring(0, g.location.Length - 1));
            //        g.graveId = gv.id;
            //    }
            //});

            et = DateTime.Now;
            ts = et - st;
            sw.WriteLine(et.ToString("<br/>保存时间：yyyy-MM-dd HH:mm:ss"));
            sw.WriteLine(string.Format("<br/>总耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds));
            sw.Close();
        }



        private string getCellVaue(ICell cell)
        {
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = cell.ToString();
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

        protected void btnUpload2_Click(object sender, EventArgs e)
        {
            Cfg.IsRunning = false;
            lblMsg.Text = "";
            linkUpload2.Text = "";
            linkUpload2.NavigateUrl = "";
            UserEntity user = UserService.GetUser(HttpContext.Current);
            if (user == null) return;
            //文件名  
            string fileExt = System.IO.Path.GetExtension(fileUpload2.FileName);
            string dir = txtDir.Text;
            if (!dir.EndsWith("/")) dir += "/";
            string d = Server.MapPath(dir);
            if (!System.IO.Directory.Exists(d))
            {
                System.IO.Directory.CreateDirectory(d);
            }
            string fileName = fileUpload2.FileName;
            string oldfname = Server.MapPath(dir) + fileName;
            string fname = Server.MapPath(dir) + "_" + fileName;
            fileUpload2.SaveAs(oldfname);

            linkUpload2.NavigateUrl = dir + fileName;
            linkUpload2.Text = dir + fileName;
            lblMsg.Text = fileName + "上传成功！";
        }

        private void save(string oldfname, string fname)
        {
            System.IO.File.Delete(oldfname);
            System.IO.File.Move(fname, oldfname);
        }

        protected void btnUploadLayout_Click(object sender, EventArgs e)
        {
            Cfg.IsRunning = false;
            lblMsg.Text = "";
            linkUpload3.Text = "";
            linkUpload3.NavigateUrl = "";
            UserEntity user = UserService.GetUser(HttpContext.Current);
            if (user == null) return;
            //文件名  
            string fileExt = System.IO.Path.GetExtension(fileLayoutUpLoad.FileName);
            string dir = txtDir.Text;
            if (!dir.EndsWith("/")) dir += "/";
            string d = Server.MapPath(dir);
            if (!System.IO.Directory.Exists(d))
            {
                System.IO.Directory.CreateDirectory(d);
            }
            string fileName = fileLayoutUpLoad.FileName;
            if (string.IsNullOrEmpty(fileName)) return;
            string oldfname = Server.MapPath(dir) + fileName;
            string fname = Server.MapPath(dir) + "_" + fileName;
            fileLayoutUpLoad.SaveAs(oldfname);

            linkUpload3.NavigateUrl = dir + fileName;
            linkUpload3.Text = dir + fileName;
            lblMsg.Text = fileName + "上传成功！";
        }

        private void createLayout() {
            List<int> excludes = new List<int>();
            string exs = txtExclude.Text;
            if (!string.IsNullOrEmpty(exs))
            {
                string[] ss = exs.Split(',');
                foreach (var s in ss)
                {
                    excludes.Add(int.Parse(s));
                }
            }
            Response.Write("<br/>start[0]");
            Response.Flush();
            string path = Server.MapPath("/admin/upload/log.html");
            StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create), Encoding.UTF8);
            DateTime st = DateTime.Now;
            sw.WriteLine(st.ToString("初始化开始时间：yyyy-MM-dd HH:mm:ss"));
            int cnt = 0;
            Cfg.IsRunning = true;
            foreach (ParadiseLayoutModel p in graveList)
            {
                var pp = DB.tbParadise.FirstOrDefault(m => m.id == p.id);
                string pcode = pp.code;
                List<int> list = new List<int>();
                string[] ls = p.lines.Split(',');

                char c = Convert.ToChar('A' + p.gtype - 1);
                StringBuilder sb = new StringBuilder();
                Dictionary<string, int> hGrave = new Dictionary<string, int>();
                foreach (string line in ls)
                {  //排
                    int tlayers = p.layers;
                    for (int lay = 1; lay <= tlayers; lay++)
                    {
                        if (excludes.Any(m => (lay + "").IndexOf(m + "") >= 0)) { tlayers++; continue; }
                        int tcols = p.startIndex + p.columns;
                        for (int col = p.startIndex; col < tcols; col++)
                        {
                            if (excludes.Any(m => (col + "").IndexOf(m + "") >= 0)) { tcols++; continue; }
                            int num = col;
                            if (p.endNum != 0)
                            {
                                num = num * 10 + p.endNum;
                            }
                            tbGrave g = new tbGrave()
                            {
                                paradiseId = p.id,
                                location = string.Format("{0:000}{1:0}0{2:00}{3:00}{4:00000}{5:0}", pcode, p.dong, p.section, int.Parse(line), num, c),
                                name = string.Format("{0:00}区{1:00}排{2:00000}号", p.section, int.Parse(line), num),
                                hourseNum = string.Format("{0:0}", p.dong),
                                floorNum = string.Format("{0:0}", p.floor),
                                sectionNum = string.Format("{0:00}", p.section),
                                lineNum = string.Format("{0:00}", int.Parse(line)),
                                layerNum = string.Format("{0:00}", lay),
                                columnNum = string.Format("{0:000}", num),
                                qty = 0,
                                graveType = p.gtype
                            };
                            if (pp.type == "骨灰堂")
                            {
                                g.location = string.Format("{0}{1}{2}{3}{4}{5}{6:000}{7}", pcode, g.hourseNum, g.floorNum, g.sectionNum, g.lineNum, g.layerNum, int.Parse(g.columnNum), c);
                                g.name = string.Format("{1}栋{2}楼{3}区{4}排{5}层{6:000}号", pcode, g.hourseNum, g.floorNum, g.sectionNum, g.lineNum, g.layerNum, int.Parse(g.columnNum));
                                g.columnNum = string.Format("{0:000}", int.Parse(g.columnNum));
                            }
                            else
                            {
                                int layer = num / 1000;
                                int cn = num % 1000;
                                g.floorNum = "0";
                                g.layerNum = string.Format("{0:00}", layer);
                                g.columnNum = string.Format("{0:000}", cn);

                            }
                            var gg = DB.tbGrave.FirstOrDefault(m => m.location == g.location);
                            if (gg == null) DB.tbGrave.Add(g);
                            else
                            {
                                sb.AppendLine(g.location);
                            }
                            cnt++;
                            sw.WriteLine("<br/>"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss ") + g.location);
                            if (cnt % 200 == 0)
                            {
                                DB.SaveChanges();
                                sw.WriteLine(string.Format("<br/>{0}[{1}]", g.name, cnt));
                                sw.Flush();
                                Response.Write("[" + cnt + "] ");
                                Response.Flush();
                            }
                            if (!Cfg.IsRunning) return;
                        }
                    }
                }//foreach (string line in ls)                

            }// end foreach
            DB.SaveChanges();
            Response.Write("<br/>end[" + cnt + "] ");
            Response.Flush();
            sw.WriteLine(string.Format("<br/>{0}]", cnt));
            DateTime et = DateTime.Now;
            TimeSpan ts = et - st;
            sw.WriteLine(et.ToString("<br/>结束时间：yyyy-MM-dd HH:mm:ss"));
            sw.WriteLine(string.Format("<br/>耗时:{0}分{1}秒{2}微秒", ts.Minutes, ts.Seconds, ts.Milliseconds));
            sw.Flush();
            sw.Close();
        }
        protected void btnCreateLayout_Click(object sender, EventArgs e)
        {
            Thread th = new Thread(createLayout);
            th.Start();
            th.Join();
        }

        protected void btnParseFile_Click(object sender, EventArgs e)
        {
            try
            {
                var list = parseLayoutExcel();
                if (list.Count > 0)
                {
                    graveList.Clear();
                    graveList.AddRange(list);
                }
                lblMsg.Text = "解析成功！";
            }
            catch (Exception ex)
            {
                lblMsg.Text = "解析出错，请查询/admin/upload/log.html日志！";
            }
        }
        public List<ParadiseLayoutModel> parseLayoutExcel()
        {

            string strFileName = Server.MapPath(linkUpload3.Text);

            int SheetIndex = 0;
            int HeaderRowIndex = 1;

            List<ParadiseLayoutModel> list = new List<ParadiseLayoutModel>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;
                string[] headers = { "骨灰堂名", "栋号", "楼层", "厅区", "排号", "每排层数", "每层数量", "穴位类型", "开始编号", "末位数字" };

                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;

                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    ParadiseLayoutModel m = new ParadiseLayoutModel();
                    m.lineNum = i;
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            throw new Exception(string.Format("第{0}行无数据!<br/>", i + 1));
                        }

                        //名称
                        m.name = getCellVaue(row.GetCell(0));
                        //处理名称
                        var c = DB.tbParadise.FirstOrDefault(p => p.name == m.name);
                        if (c == null)
                        {
                            throw new Exception(string.Format("第{0}行【{1}】名称无效! ", i + 1, m.name));
                        }
                        else
                        {
                            m.id = c.id;
                            m.code = c.code;
                        }

                        string dong = getCellVaue(row.GetCell(1));
                        m.dong = int.Parse(dong);

                        string floor = getCellVaue(row.GetCell(2));
                        m.floor = int.Parse(floor);

                        string section = getCellVaue(row.GetCell(3));
                        m.section = int.Parse(section);

                        string line = getCellVaue(row.GetCell(4));
                        m.lines = line;
                        if (string.IsNullOrEmpty(line))
                        {
                            throw new Exception(string.Format("第{0}行排号不能为空! ", i + 1));
                        }
                        else
                        {
                            string[] ss = line.Split(',');
                            if (ss.Length < 1)
                            {
                                throw new Exception(string.Format("第{0}行排号有错! ", i + 1));
                            }
                        }

                        string layer = getCellVaue(row.GetCell(5));
                        m.layers = int.Parse(layer);

                        string col = getCellVaue(row.GetCell(6));
                        m.columns = int.Parse(col);

                        string type = getCellVaue(row.GetCell(7));
                        if (type == "单") m.gtype = 1;
                        else if (type == "双") m.gtype = 2;
                        else m.gtype = 3;

                        string start = getCellVaue(row.GetCell(8));
                        m.startIndex = int.Parse(start);

                        string endNum = getCellVaue(row.GetCell(9));
                        m.endNum = int.Parse(endNum);

                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据格式错误!" + ex1.Message, i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString() + "<br/>");
                        sbLine.Clear();
                    }
                    else
                    {
                        list.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("数据格式有误:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("数据格式有误");
            }
            sheet = null;
            return list;
        }


    }
}